import frappe
from frappe.utils import add_days, get_datetime_str, getdate


def delete_obsolete_mrp():
    last_week = add_days(getdate(), - 5)
    sql = """delete pi from `tabPlan Order Item` pi 
            inner join `tabPlan Order` p on pi.parent=p.name
            inner join `tabMulti Level MRP` m on p.mrp=m.name 
            where is_fixed = 0 and m.creation < '{0}'""".format(last_week)
    sql1 = """delete p from `tabPlan Order` p 
            inner join `tabMulti Level MRP` m on p.mrp=m.name 
            where is_fixed = 0 and m.creation < '{0}'""".format(last_week)
    sql2 = """delete p from `tabMRP Inventory` p 
            inner join `tabMulti Level MRP` m on p.mrp=m.name
            where m.creation < '{0}'""".format(last_week) 
    sql3 = """delete p from `tabMRP Plan Item` p 
            inner join `tabMulti Level MRP` m on p.parent=m.name
            where m.creation < '{0}'""".format(last_week)  
    sql4 = """delete r from `tabMRP Run Log` r 
            inner join `tabMulti Level MRP` m on r.parent = m.name
            where m.creation < '{0}'""".format(last_week)  
    sql5 = """delete m from `tabMulti Level MRP` m 
            where m.creation < '{0}'""".format(last_week)                      
    for s in [sql, sql1, sql2, sql3, sql4, sql5]:
        try:
            frappe.db.sql(s)
        except Exception as e:
            print("sql = ", s)
            print("error=", str(e))
    frappe.db.commit()